PHP MYSQL database function

Posted on | In

some function about how to use PHP and MYSQL database

PHP and MYSQL

connect to mysql server

1
2
3
4
5
6
7
8
9
10
11
12
<?php
$dbhost = 'localhost';  // server
$dbuser = 'root';            // username
$dbpass = '123456';          // password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Could not connect: ' . mysqli_error());
}
echo 'yes';
mysqli_close($conn);
?>

creat database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?php
$dbhost = 'localhost:3306';  // server
$dbuser = 'root';            // username
$dbpass = '123456';          // password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('error ' . mysqli_error($conn));
}
echo 'connect yes';
$sql = 'CREATE DATABASE RUNOOB';
$retval = mysqli_query($conn,$sql );
if(! $retval )
{
    die('error ' . mysqli_error($conn));
}
echo "yes";
mysqli_close($conn);
?>

delete database

1
$sql = 'DROP DATABASE RUNOOB';

select database

1
2
3
4
5
6
7
8
9
10
11
12
13
<?php
$dbhost = 'localhost';  // server
$dbuser = 'root';            // username
$dbpass = '123456';          // password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Could not connect: ' . mysqli_error());
}
echo 'yes';
mysqli_select_db($conn, 'RUNOOB' );
mysqli_close($conn);
?>

create database table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<?php
$dbhost = 'localhost';  // server
$dbuser = 'root';            // username
$dbpass = '';          // password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('error ' . mysqli_error($conn));
}
echo 'connect yes';
$sql = "CREATE TABLE runoob_tbl( ".
        "runoob_id INT NOT NULL AUTO_INCREMENT, ".
        "runoob_title VARCHAR(100) NOT NULL, ".
        "runoob_author VARCHAR(40) NOT NULL, ".
        "submission_date DATE, ".
        "PRIMARY KEY ( runoob_id ))ENGINE=InnoDB DEFAULT CHARSET=utf8; ";
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('error: ' . mysqli_error($conn));
}
echo "success";
mysqli_close($conn);
?>

delete database table

1
2
3
4
5
6
7
8
$sql = "DROP TABLE runoob_tbl";
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
  die('error ' . mysqli_error($conn));
}
echo "success";

insert data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<?php
$dbhost = 'localhost';  // server
$dbuser = 'root';            // username
$dbpass = '';          // password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('error ' . mysqli_error($conn));
}
echo 'connect yes';
// Chinese
mysqli_query($conn , "set names utf8");
 
$runoob_title = 'ѧϰ Python';
$runoob_author = 'RUNOOB.COM';
$submission_date = '2016-03-06';
 
$sql = "INSERT INTO runoob_tbl ".
        "(runoob_title,runoob_author, submission_date) ".
        "VALUES ".
        "('$runoob_title','$runoob_author','$submission_date')";
 
 
 
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
  die('u' . mysqli_error($conn));
}
echo "s";
mysqli_close($conn);
?>

search data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<?php
$dbhost = 'localhost';  // server
$dbuser = 'root';            // username
$dbpass = '';          // password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('error ' . mysqli_error($conn));
}
echo 'connect yes';
// Chinese
mysqli_query($conn , "set names utf8");
$sql = 'SELECT runoob_id, runoob_title, 
        runoob_author, submission_date
        FROM runoob_tbl';
 
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('unable ' . mysqli_error($conn));
}
echo '<h2>mysqli_fetch_assoc test<h2>';
echo '<table border="1"><tr><td>rn ID</td><td>title</td><td>author</td><td>date</td></tr>';
while($row = mysqli_fetch_assoc($retval))
{
    echo "<tr><td> {$row['runoob_id']}</td> ".
         "<td>{$row['runoob_title']} </td> ".
         "<td>{$row['runoob_author']} </td> ".
         "<td>{$row['submission_date']} </td> ".
         "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 results";
}
mysqli_close($conn);
?>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$sql = 'SELECT runoob_id, runoob_title, 
        runoob_author, submission_date
        FROM runoob_tbl';
 
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('�޷���ȡ����: ' . mysqli_error($conn));
}
echo '<h2>�����̳� mysqli_fetch_array ����<h2>';
echo '<table border="1"><tr><td>�̳� ID</td><td>����</td><td>����</td><td>�ύ����</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_NUM))
{
    echo "<tr><td> {$row[0]}</td> ".
         "<td>{$row[1]} </td> ".
         "<td>{$row[2]} </td> ".
         "<td>{$row[3]} </td> ".
         "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

free internal memory

1
2
3
mysqli_free_result($retval);
mysqli_close($conn);

Delete Data From MySQL

1
2
3
4
5
6
7
8
9
$sql = "DELETE FROM MyGuests WHERE id=3";
if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . mysqli_error($conn);
}
mysqli_close($conn);

Update Data in MySQL

1
2
3
4
5
6
7
8
9
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
if (mysqli_query($conn, $sql)) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . mysqli_error($conn);
}
mysqli_close($conn);

where syntax

1
2
3
4
$sql = 'SELECT runoob_id, runoob_title, 
        runoob_author, submission_date
        FROM runoob_tbl
        WHERE runoob_author="RUNOOB.COM"';

like syntax

1
2
3
4
$sql = 'SELECT runoob_id, runoob_title, 
        runoob_author, submission_date
        FROM runoob_tbl
        WHERE runoob_author LIKE "%COM"';// % means any

MySQL UNION

1
2
3
4
5
6
7
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

different country

1
2
3
4
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

all country

1
2
3
4
SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

1
2
3
4
5
6
SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

order by

1
2
3
4
5
6
7
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
SELECT runoob_id, runoob_title, 
runoob_author, submission_date
FROM runoob_tbl
ORDER BY  submission_date ASC';

GROUP BY

1
2
3
4
5
6
7
8
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;

INNER JOIN

1
$sql = 'SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author';

NULL

1
2
3
4
5
6
7
8
9
10
11
12
if( isset($runoob_count ))
{
   $sql = "SELECT runoob_author, runoob_count
           FROM  runoob_test_tbl
           WHERE runoob_count = $runoob_count";
}
else
{
   $sql = "SELECT runoob_author, runoob_count
           FROM  runoob_test_tbl
           WHERE runoob_count IS NULL";
}

ALTER

1
2
3
4
5
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);
mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
mysql> ALTER TABLE testalter_tbl ENGINE = MYISAM;;
mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'\G

index

[links] (http://www.runoob.com/mysql/mysql-index.html)

clone table

[links] (http://www.runoob.com/mysql/mysql-clone-tables.html)

database export

1
2
mysql> SELECT * FROM runoob_tbl 
    -> INTO OUTFILE '/tmp/tutorials.txt';

database import

1
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

handling duplicates

[links] (http://www.runoob.com/mysql/mysql-handling-duplicates.html)

using sequences

[links] (http://www.runoob.com/mysql/mysql-using-sequences.html)

sql injection

[links] (http://www.runoob.com/mysql/mysql-sql-injection.html)

database info

[links] (http://www.runoob.com/mysql/mysql-database-info.html)